package com.pxmeta.party.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.pxmeta.party.entity.ActivityAdAstraRecord;
import com.pxmeta.party.entity.vo.ActivityAdAstraDataLateItemVO;
import com.pxmeta.party.entity.vo.ActivityAdAstraStarPriceVO;
import com.pxmeta.party.entity.vo.ActivityAdAstraStarVO;
import com.pxmeta.user.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ActivityAdAstraRecordMapper extends BaseMapper<ActivityAdAstraRecord> {


    @Select("SELECT\n" +
            "\tt2.*,\n" +
            "\tsum( t1.count ) totalCount \n" +
            "FROM\n" +
            "\tkt_activity_ad_astra_user_record t1 \n" +
            "\tleft join kt_user t2 on t1.user_id = t2.id\n" +
            "GROUP BY\n" +
            "\tt1.user_id \n" +
            "ORDER BY\n" +
            "\ttotalCount DESC")
    List<User> getRankingUsers(@Param("page") Page<User> page);


    @Select("SELECT\n" +
            "\tt2.`name` starName,\n" +
            "\tsum(t1.count) count\n" +
            "FROM\n" +
            "\tkt_activity_ad_astra_user_record t1 \n" +
            "\tLEFT JOIN kt_activity_ad_astra t2 on t1.star_id = t2.id\n" +
            "where t1.user_id = #{userId}\n" +
            "group by t1.star_id")
    List<ActivityAdAstraStarVO> getUserStarList(@Param("userId") Long userId);

    @Select("SELECT\n" +
            "\tsum( reward_price ) totalRewardPrice,\n" +
            "\tstar_id starId \n" +
            "FROM\n" +
            "\tkt_activity_ad_astra_user_record \n" +
            "WHERE\n" +
            "\trecord_id = #{recordId} \n" +
            "GROUP BY\n" +
            "\tstar_id \n" +
            "ORDER BY\n" +
            "\ttotalRewardPrice ASC \n" +
            "\tLIMIT 1")
    ActivityAdAstraStarPriceVO selectMinStarByRecordId(@Param("recordId") Long recordId);

    @Select("SELECT * from kt_activity_ad_astra_record   WHERE create_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) and status = 1")
    List<ActivityAdAstraRecord> getHourRecordList();

    @Select("SELECT * from kt_activity_ad_astra_record   WHERE DATE(create_time) = CURDATE() and status = 1 order by create_time desc")
    List<ActivityAdAstraRecord> getTodayRecordList();

    @Select("SELECT * from kt_activity_ad_astra_record   WHERE DATE(create_time) = DATE(NOW() - INTERVAL 1 DAY) and status = 1")
    List<ActivityAdAstraRecord> getLastDayRecordList();


    @Select("SELECT \n" +
            "    success_id as id,\n" +
            "    DATE_FORMAT(create_time,'%m/%d %H:%i') as time,\n" +
            "    create_time as dateTime,\n" +
            "    TIMESTAMPDIFF(MINUTE, prev_time, create_time) AS count\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        success_id,\n" +
            "        create_time,\n" +
            "        LAG(create_time) OVER (PARTITION BY success_id ORDER BY create_time) AS prev_time\n" +
            "    FROM kt_activity_ad_astra_record\n" +
            "\t\tWHERE create_time >= NOW() - INTERVAL 24 HOUR\n" +
            "\t\tand success_id in ('1','5','7','8') and status = 1 \n" +
            ") AS subquery\n" +
            "ORDER BY success_id,create_time;")
    List<ActivityAdAstraDataLateItemVO> getLateRecordList();
}
